package com.dhcc.DRGsHosp.application.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.inject.Named;

import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.DailyRecordApplication;
import com.dhcc.DRGsHosp.application.DepartmentApplication;
import com.dhcc.DRGsHosp.application.DrgsIndexCalApplication;
import com.dhcc.DRGsHosp.core.domain.DailyRecord;
import com.dhcc.DRGsHosp.core.domain.Department;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.GlobalData;
import com.dhcc.DRGsHosp.infra.util.StringUtils;

@Named("dailyRecordApplicationImplqiaoyilong20181026")
@Transactional
public class DailyRecordApplicationImpl implements DailyRecordApplication {
	@Autowired
	private JdbcTemplateObj jdbcTemplateObj;// 使用DRGS_HOSP_KOALA用户连接数据源
	@Autowired
	private DepartmentApplication departmentApplication;
	@Autowired
	private DrgsIndexCalApplication drgsIndexCalApplication;
	public DailyRecord getDailyRecord(String drEpisodeid) {
		return DailyRecord.get(DailyRecord.class, drEpisodeid);
	}

	public void creatDailyRecord(DailyRecord dailyRecord) {
		dailyRecord.save();
	}

	public void updateDailyRecord(DailyRecord dailyRecord) {
		dailyRecord.save();
	}

	public void removeDailyRecord(DailyRecord dailyRecord) {
		if (dailyRecord != null) {
			dailyRecord.remove();
		}
	}

	public void removeDailyRecords(Set<DailyRecord> dailyRecords) {
		for (DailyRecord dailyRecord : dailyRecords) {
			dailyRecord.remove();
		}
	}

	public List<DailyRecord> findAllDailyRecord() {
		return DailyRecord.findAll(DailyRecord.class);
	}

	private QueryChannelService queryChannel;

	private QueryChannelService getQueryChannelService() {
		if (queryChannel == null) {
			queryChannel = InstanceFactory.getInstance(QueryChannelService.class, "queryChannel");
		}
		return queryChannel;
	}

	/**
	 * 
	 * @Description：全院各科室 监测数据,depcodes为科室角色授权
	 * @Author mazhaoyan
	 * @see com.dhcc.DRGsHosp.application.DailyRecordApplication#getDepShow(String,
	 *      String)
	 */
	@Override
	public List<Map<String, Object>> getDepShow(String fromdate, String todate, String hospCode,String depCodes) {
		//所有科室
		List<Department> depList = departmentApplication.findAllDepartment();
		Map<String, String> depMapCodeName = new HashMap<String, String>();//科室名称代码信息
		for (Department dep : depList) {
			depMapCodeName.put(dep.getDepCode(),dep.getDepName());
		}
		// 科室名称条件
		List<String> depNamesList = new ArrayList();
		// 科室编码条件
		List<String> depCodesAList = Arrays.asList(depCodes.split(",")) ;
		List<String> depCodesList = new ArrayList(depCodesAList);
		String fromdatecc = DrgsIndex.getDateFromCc(fromdate, "year") ;
		String todatecc = DrgsIndex.getDateFromCc(todate, "year") ;
		String year = fromdate.substring(0, 4);// 年份
		String lastyear = Integer.parseInt(year) - 1 + "";// 去年
		String manageYear = year;
		String manageYearSql = "select count(t.qm_id) from DRGs.t_Quotamanage t where t.manage_year='" + year + "' and patient_type=0";
		int count = jdbcTemplateObj.queryForObject(manageYearSql, Integer.class);
		if (count == 0) {
			manageYearSql = "select max(t.manage_year) manageyear from DRGs.t_Quotamanage t where pardrg_code is null and pardep_code is not null  and patient_type=0";
			String mYear = (String) getQueryChannelService().createSqlQuery(manageYearSql).singleResult();
			manageYear = mYear;
		}

		// sicu&ICUθ妇产科&综合科θ妇产科:CKEBQ,CKBQ&综合科:GDWKYBQθ

		// 取系统配置，取得第二类科室配置
		String dayMonitor_dep = GlobalData.sysConfig.get("DAYMONITOR_DEP");
		// String sp_dep1 = "";
		String sp_dep2 = "";// 第二类科室
		String sp_dep3 = "";// 第二类科室的对照科室
		String sp_dep4 = "";// 医保类型
		List<String> spDepList1 = new LinkedList<String>();// 第二类科室名称
		Map<String, Object> mapdep = new HashMap<>();// 第二类科室名称，对应的科室code
		// 对获取的配置做截取处理，处理成二类科室为list，二类科室和对应的科室代码为map的形式
		if (dayMonitor_dep != null && dayMonitor_dep.split("θ").length > 3) {// 配置了第二类科室、第二类科室的对照科室、医保类型
			sp_dep2 = dayMonitor_dep.split("θ")[1];
			sp_dep3 = dayMonitor_dep.split("θ")[2];
			sp_dep4 = dayMonitor_dep.split("θ")[3];
		} else if (dayMonitor_dep != null && dayMonitor_dep.split("θ").length > 2) {// 配置了第二类科室、第二类科室的对照科室
			sp_dep2 = dayMonitor_dep.split("θ")[1];
			sp_dep3 = dayMonitor_dep.split("θ")[2];
		} else if (dayMonitor_dep != null && dayMonitor_dep.split("θ").length > 2) {// 只配置了第二类科室
			sp_dep2 = dayMonitor_dep.split("θ")[1];
		}

		if (!sp_dep2.isEmpty()) {
			spDepList1 = Arrays.asList(sp_dep2.split("&"));
		}
		if (!sp_dep3.isEmpty()) {
			for (int i = 0; i < spDepList1.size(); i++) {
				String sp_dep2temp = sp_dep3.split("&")[i];
				String sp_depA = sp_dep2temp.split(":")[0];
				String sp_depcode = sp_dep2temp.split(":")[1];
				mapdep.put(sp_depA, sp_depcode);
			}
		}
		if (!sp_dep4.isEmpty()) {
			sp_dep4 = "'" + sp_dep4 + "'";
			sp_dep4 = sp_dep4.replace("&", "','");
		}
		String sqlAcc = "";
		String sqlAcc2 = "";
		String sqlAcc3 = "";
		String sql = " select ddep.dep_code,abc.OUTSUM,abc.NODRUG_COST,abc.DRUG_COST,abc.TOTAL_COST,abc.DR_OUT_DEP,abc.BEDDAYS,abc.PATIENTSUM, \n "
				+ " abc.AVGBEDDAYS,abc.AVGCOST,abc.DRUGRATION,abc.SUM_OUTSUM,abc.SUM_AVGBEDDAYS,abc.SUM_AVGCOST,abc.SUM_DRUGRATION, \n "
				+ " abc.ADEPCODE,abc.ADEPGROUPCODE,abc.DRUGRATIO,abc.HIAVGCOST,abc.ACCAVGBEDDAYS,abc.ACCAVGCOST,abc.ACCDRUGRATIO, \n "
				+ " abc.PARDEP_CODE,abc.BAVGCOST,abc.BAVGDAYS,abc.BDRUGCOSTRATION,abc.BAVGHICOST,abc.DEP_NAME,abc.BDRUGCOSTRATIO, \n "
				+ " abc.CURSTAND,abc.ACCSTAND,abc.GROW,abc.QUOTA_DETAIL_SUM,abc.DEPIN,abc.DEPOUT, \n " + " dd.depgroup_desc, ddep.pardepgroup_code,overnum.AADEP,overnum.OVERBENCHMARK,overnum.NOTOVER  from ( \n "
				+ " select a.OUTSUM,a.NODRUG_COST,a.DRUG_COST,a.TOTAL_COST,a.DR_OUT_DEP,a.BEDDAYS,a.PATIENTSUM,a.AVGBEDDAYS, \n "
				+ " a.AVGCOST,a.DRUGRATION,a.SUM_OUTSUM,a.SUM_AVGBEDDAYS,a.SUM_AVGCOST,a.SUM_DRUGRATION,a.ADEPCODE,a.ADEPGROUPCODE, \n "
				+ " round(a.drugRation * 100, 2) || '%' as drugRatio,hi.hiavgCost, acc.accavgbeddays, acc.accavgcost,round(acc.accdrugration * 100, 2) || '%' as AccdrugRatio, \n "
				+ " quotaM.pardep_code,quotaM.bAvgCost,quotaM.bAvgDays,quotaM.bDrugCostRation,quotaM.bAvgHICost,quotaM.dep_name,round(quotaM.bDrugCostRation, 2) || '%' as bDrugCostRatio, \n "
				+ " round(a.avgCost - nvl(quotaM.bAvgCost,0), 2) as curStand, round(a.avgCost - acc.AccavgCost, 2) as accStand, \n "
				+ " --解决除数为0问题 \n" // -- 解决除数为0问题
				+ " round(( decode(complete13.quota_detail_sum,0,null,(a.avgCost - complete13.quota_detail_sum) / complete13.quota_detail_sum)), 2) as grow, \n "
				+ " round(complete13.quota_detail_sum, 2) as quota_detail_sum ,nvl(depin.incnt,0) as depin,nvl(depout.outcnt,0) as depout from  \n "
				+ " --当期值计算 \n"
				+ " (select aa.outsum,aa.nodrug_cost,aa.drug_cost,aa.total_cost,aa.DR_OUT_DEP,aa.beddays,aa.patientsum,aa.avgbeddays,aa.avgCost,aa.drugRation,aa1.sum_outsum,aa1.sum_avgbeddays,aa1.sum_avgCost,aa1.sum_drugRation,drgd.dep_code as  adepcode,drgd.pardepgroup_code  as adepgroupcode from \n "
				+ " (select count(t.dr_total_cost) as outsum, round(sum(t.dr_nodrug_cost), 2) as nodrug_cost, \n " + " round(sum(t.dr_drug_cost), 2) as drug_cost,round(sum(dr_total_cost), 2) as total_cost, \n "
				+ " DR_OUT_DEP, round(sum(dr_out_date - dr_in_date), 2) as beddays, count(DR_EPISODE_ID) as patientsum, \n " + " round(sum(dr_out_date - dr_in_date) / count(dr_mr_no),2) as avgbeddays, \n "
				+ " round(sum(dr_total_cost) / count(DR_EPISODE_ID), 2) as avgCost, \n " + " case when sum(dr_total_cost)>0 then round(sum(t.dr_drug_cost) / sum(dr_total_cost), 2) else 0 end as drugRation \n "
				+ " from DRGs.T_DAILYRECORD t  where 1=1  \n " + " and to_char(t.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "' and '" + todate + "' and instr('" + hospCode
				+ "',t.dr_hospcode)>0  and t.dr_is_abnormal!='1' group by DR_OUT_DEP) aa  \n "
				+ " --计算累计数据  \n"
				// 计算累计数据
				+ " left join (select count(t.P00800000) as sum_outsum,P00300000 OUT_DEP,round(sum(P00290000 - P00250000) /count(t.PMAININDEX), 2) as sum_avgbeddays,  \n "
				+ " round(sum(P00800000) / count(t.PMAININDEX),2) as sum_avgCost,to_char(round(sum(es.drug_cost) / sum(P00800000)*100,2)||'%') as sum_drugRation  \n "
				+ " from DRGs.MR_IPINFO t left join drgs.mr_expense_sum es on t.PMAININDEX = es.pmainindex where 1=1 \n " + " and to_char(t.P00290000,'yyyy-mm-dd') between '" + year + "-01-01' and '" + todate + "'  \n "
				+ " group by P00300000) aa1 on aa1.out_dep=aa.dr_out_dep  \n " + " left join DRGs.t_department drgd on aa.dr_out_dep=drgd.dep_name) a  \n "
				+ " --医保数据计算  \n"
				+ " left join (select round(sum(b.dr_total_cost) /count(b.DR_EPISODE_ID), 2) as hiavgCost, \n " + " DR_OUT_DEP  from DRGs.T_DAILYRECORD b   \n ";
		if (!sp_dep4.isEmpty()) {
			sql += " where b.dr_patient_type in (" + sp_dep4 + ") \n";
		}
		sql += " group by b.dr_out_dep) hi  on a.dr_out_dep = hi.dr_out_dep \n "
				// -- 累计值计算--下列代码计算的是当前查询日期的累计值
				+ " left join ( \n " + " select dep_name, dep_code, avgdays accavgbeddays, avgfee accavgcost,  drugRatio accdrugration, hcAvgFee accAvgHICost \n " + " from(  \n "
				+ " select a.dep_code,a.dep_name,a.TotalFee,a.DrugFee,a.avgdays,a.avgfee,a.hccostamount,a.hccount, case when TotalFee<>0 then DrugFee/TotalFee*100 else 0 end drugRatio, \n "
				+ " case when hccount=0 then 0 else hccostamount/hccount end hcAvgFee \n " + " from ( select a.P00300001 dep_code, a.P00300000 dep_name, \n " + " sum(a.P00800000)/10000 as TotalFee,  \n "
				+ " sum(es.drug_cost)/10000 as DrugFee, \n " + " avg(P00320000) avgdays,  \n " + " avg(P00800000) avgfee, \n " + " sum(case when d.parid is not null then P00800000 else 0 end) hccostamount,  \n "
				+ " sum(case when d.parid is not null then 1 else 0 end) hccount  \n " + " from DRGs.Mr_Ipinfo a   \n " + " left join DRGs.t_patientdrg b  \n "
				+ " on a.pmainindex=b.parid  left join drgs.mr_expense_sum es on es.pmainindex = a.pmainindex \n " + " left join DRGs.t_medins d  \n " + " on d.parid=a.pmainindex  \n " + " where 1=1  \n "
				+ " and to_char(a.P00290000,'yyyy')='" + year + "'  \n " + " group by P00300001,P00300000 \n " + " ) a))acc on a.dr_out_dep = acc.dep_name \n "
				// --标杆值
				+ " left join (select quotaM.pardep_code,quotaM.bAvgCost,quotaM.bAvgDays,quotaM.bDrugCostRation,quotaM.bAvgHICost, dep.dep_name   from (select pardep_code, round(max(decode(parquota_code, \n "
				+ " 'AvgFee', manage_benchmark, 0)),2) bAvgCost, round(max(decode(parquota_code,'AvgDays', manage_benchmark, \n " + " 0)), 2) bAvgDays, round(max(decode(parquota_code, 'DrugRatio', manage_benchmark, \n "
				+ " 0)), 2) bDrugCostRation,round(max(decode(parquota_code, 'HCAvgFee', manage_benchmark, 0)),2) bAvgHICost \n "
				+ " from DRGs.t_quotamanage where pardep_code is not null and pardrg_code is null and patient_type=0 \n " + " and manage_year = '" + manageYear
				+ "' group by pardep_code) quotaM,  DRGs.t_department dep \n " + " where quotaM.pardep_code = dep.dep_code) quotaM  on a.dr_out_dep = quotaM.dep_name \n "
				// --科室转入
				+ "  left join (select distinct(t.dep_code), count(*) over(partition by t.dep_code) incnt \n " + " from drgs.T_DEPARTMENT t left join drgs.t_dailyrecord d \n "
				+ "  on t.dep_name = d.dr_out_dep where t.dep_name!=d.dr_in_dep  \n " + " and to_char(d.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "' and '" + todate + "') depin \n "
				+ " on a.adepcode=depin.dep_code \n "
				// 科室转出
				+ " left join (select distinct(t.dep_code),   count(*) over(partition by t.dep_code) outcnt \n " + "  from drgs.T_DEPARTMENT t left join drgs.t_dailyrecord d  on t.dep_name = d.dr_in_dep \n "
				+ "  where t.dep_name!=d.dr_out_dep  \n " + " and to_char(d.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "' and '" + todate + "'  \n " + " )depout on a.adepcode=depout.dep_code \n ";
		// --13年完成值
		// 下面是调完指标名称的
		// --13年完成值
		sqlAcc = " left join ( \n " + " select a.P00300001 pardep_code, avg(a.P00800000) quota_detail_sum  \n " + " from DRGs.Mr_Ipinfo a  \n " + " where 1=1  \n " + " and to_char(a.P00290000,'yyyy') = '" + lastyear
				+ "'  \n " + " group by P00300001 ) complete13  \n " + " on a.adepcode = complete13.pardep_code) abc left join DRGs.t_department ddep \n "
				+ " on abc.dr_out_dep = ddep.dep_name left join DRGs.t_depgroup dd  on \n " + " ddep.pardepgroup_code = dd.depgroup_code  \n "
				+ " left join (select dr_out_dep aadep,sum(overbenchmark) overbenchmark ,sum(notover) notover from (  \n "
				+ " select dr.dr_out_dep ,case when dr.dr_total_cost-benchmark.manage_benchmark>0 then  1 else 0 end overbenchmark,case when dr.dr_total_cost-benchmark.manage_benchmark<=0 then 1 else 0 end notover  from drgs.t_dailyrecord dr \n ";
		sqlAcc2 = " left join ( select dep_code,dep_name from  DRGs.t_department ";
		for (int i = 0; i < spDepList1.size(); i++) {
			sqlAcc2 = sqlAcc2 + "union select'dep" + i + "' dep_code,'" + spDepList1.get(i) + "' dep_name from dual  ";
		}
		sqlAcc2 = sqlAcc2 + " )dp on dr.dr_out_dep = dp.dep_name \n ";
		sqlAcc3 = " left join(select qm.pardep_code,qm.manage_benchmark from DRGs.t_quotamanage qm where qm.pardep_code is not null and qm.pardrg_code is null  and qm.patient_type=0 and qm.manage_year = '" + manageYear
				+ "' and qm.parquota_code = 'AvgFee' and qm.par_ms_code='manager_inner' \n ";

		for (int j = 0; j < spDepList1.size(); j++) {
			String depTemp = (String) mapdep.get(spDepList1.get(j));
//			depTemp = "'" + depTemp + "'";
//			depTemp.replace(",", "','");
			depTemp = StringUtils.convertToQuoteString(depTemp) ;

			sqlAcc3 = sqlAcc3 + " union select 'dep" + j + "',avg(qm.manage_benchmark) manage_benchmark from DRGs.t_quotamanage qm " + " where qm.pardrg_code is null and qm.patient_type=0 and qm.manage_year = '"
					+ manageYear + "' and qm.parquota_code = 'AvgFee' and qm.pardep_code in(" + depTemp + ") and qm.par_ms_code='manager_inner' \n";
		}
		sqlAcc3 = sqlAcc3 + " ) benchmark  on dp.dep_code= benchmark.pardep_code  where to_char(dr.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "'  \n " + " and '" + todate + "'  and instr('" + hospCode
				+ "',dr.dr_hospcode)>0 )group by dr_out_dep) overnum \n " + " on abc.dr_out_dep=overnum.aadep \n " + " order by depgroup_desc \n ";
		// 将两个sql连接进行查询
		sql = sql + sqlAcc + sqlAcc2 + sqlAcc3;
		List<Map<String, Object>> depShowList = jdbcTemplateObj.queryForList(sql);
		if (depShowList.size() != 0) {
			for (int b = 0; b < spDepList1.size(); b++) {
				String depTemp = (String) mapdep.get(spDepList1.get(b));
				// 判断是否再授权科室里
				List<String> depTempAList = Arrays.asList(depTemp.split(",")) ;
				List<String> depTempList = new ArrayList<>(depTempAList) ;
				depTempList.retainAll(depCodesList) ;
				if(depTempList.size()>0) {
					depCodesList.add(spDepList1.get(b));
					// 删除授权的小科室
					depCodesList.removeAll(depTempList);
				}
				depNamesList.add(spDepList1.get(b)) ;
				String depTempC = "";
				depTempC = "'" + depTemp + "'";
				depTempC = depTempC.replace(",", "','");
				/**
				 * 计算妇产科和综合科的次均费用--【去年】
				 */
				// 查询妇科和产科的患者总例数的和
				String sqlfckSum = "";
				sqlfckSum = " select count(t.pmainindex) from drgs.mr_baseinfo a left join DRGs.mr_ipinfo t on a.pmainindex = t.pmainindex where 1=1 ";
				sqlfckSum += " \n and to_char(t.P00290000,'yyyy-mm-dd') between '" + fromdatecc + "' and '" + todatecc + "' ";
				sqlfckSum += " \n and (t.P00300001 in (" + depTempC + ") and instr('" + hospCode + "',a.p00010001)>0 )";

				@SuppressWarnings("unused")
				Object fckSumB = getQueryChannelService().createSqlQuery(sqlfckSum).singleResult();

				// 查询去年妇科和产科次均费用
				String sqlfckFee = "";
				sqlfckFee = " select avg(P00800000) AvgFee from drgs.mr_baseinfo a left join DRGs.mr_ipinfo t on a.pmainindex = t.pmainindex where 1=1 ";
				sqlfckFee += " \n and to_char(t.P00290000,'yyyy-mm-dd') between '" + fromdatecc + "' and '" + todatecc + "' ";
				sqlfckFee += " \n and (t.P00300001 in (" + depTempC + ") and instr('" + hospCode + "',a.p00010001)>0 )";

				Object lastyearFckFeeB = getQueryChannelService().createSqlQuery(sqlfckFee).singleResult();
				float lyFckAvgCost = 0f;
				if (lastyearFckFeeB != null) {
					float lastyearFckFee = Float.parseFloat(lastyearFckFeeB.toString());
					lyFckAvgCost = (float) (Math.round(lastyearFckFee * 100)) / 100;
				}

				/**
				 * 计算妇产科和综合科的次均费用--【去年】--完成
				 */
				/**
				 * 计算妇产科，综合科当期累计的指标值
				 */
				// 查询妇科和产科的累计次均费用、平均住院日、出院病人、药占比
				String sqlfckSumLJ = "";
				sqlfckSumLJ = " select round(avg(P00320000),2) avgdays,round(avg(P00800000),2) avgfee,count(1) as totalCaseCnt, "
						+ " round(case when sum(P00800000)<>0 then sum(es.DRUG_COST)/sum(P00800000)*100 else 0 end ,2) drugRatio"
						+ " from drgs.mr_baseinfo a left join DRGs.mr_ipinfo t on a.pmainindex = t.pmainindex left join drgs.mr_expense_sum es on a.pmainindex = es.pmainindex " + " where 1=1 "
						+ " and to_char(t.P00290000,'yyyy-mm-dd') between '" + fromdatecc + "' and '" + todatecc + "'  "
						+ " and (P00300001 in (" + depTempC + ")) and instr('" + hospCode + "',a.p00010001)>0  ";
				Map<String, Object> fckSumLJMap = jdbcTemplateObj.queryForMap(sqlfckSumLJ);

				/**
				 * 计算妇产科，综合科累计的指标值 ----------- -------------完成
				 */
				/**
				 * 计算妇产科，综合科标杆的指标值
				 */
				int depCnt = 1 ;
				if(depTemp.split(",").length>1) {
					depCnt = depTemp.split(",").length ;
				}
				// 次均费用
				String bgFcksql = "select nvl(sum(manage_benchmark)/"+depCnt+",0) as bgavgcost from DRGs.t_quotamanage t " + " where t.pardrg_code is null and t.pardepgroup_code is null and patient_type=0 and pardep_code in ("
						+ depTempC + ") " + " and parquota_code='AvgFee'" + " and manage_year= '" + manageYear + "'";
				Object bgFckAvgCosttempB = getQueryChannelService().createSqlQuery(bgFcksql).singleResult();
				float bgFckAvgCosttemp = Float.parseFloat(bgFckAvgCosttempB.toString());
				float bgFckAvgCost = (float) (Math.round(bgFckAvgCosttemp * 100)) / 100;

				// 平均住院日
				String bgFckAvgDayssql = "select nvl(sum(manage_benchmark)/"+depCnt+",0) as bgavgdays from DRGs.t_quotamanage t "
						+ "where t.pardrg_code is null and t.pardepgroup_code is null and patient_type=0 and pardep_code in (" + depTempC + ") " + "and parquota_code='AvgDays'" + " and manage_year=  '" + manageYear
						+ "'";
				Object bgFckAvgDaystempB = getQueryChannelService().createSqlQuery(bgFckAvgDayssql).singleResult();
				float bgFckAvgDaystemp = Float.parseFloat(bgFckAvgDaystempB.toString());
				float bgFckAvgDays = (float) (Math.round(bgFckAvgDaystemp * 100)) / 100;

				// 药占比
				String bgFckDrugCostRatiosql = "select round(a.bgavgcost, 2) || '%'  as bgavgcost from (" + " select sum(manage_benchmark)/"+depCnt+" as bgavgcost from DRGs.t_quotamanage t "
						+ " where t.pardrg_code is null and t.pardepgroup_code is null and patient_type=0 and pardep_code in (" + depTempC + ") " + " and manage_year= '" + manageYear + "' "
						+ " and parquota_code='DrugRatio') a";
				String bgFckDrugCostRatio = getQueryChannelService().createSqlQuery(bgFckDrugCostRatiosql).singleResult().toString();

				String bgFckAvgHICostsql = "select nvl(sum(manage_benchmark)/"+depCnt+",0) as bgavgcost from DRGs.t_quotamanage t "
						+ "where t.pardrg_code is null and t.pardepgroup_code is null and patient_type=0 and pardep_code in (" + depTempC + ") " + " and manage_year= '" + manageYear + "' "
						+ "and parquota_code='HCAvgFee'";
				Object bgFckAvgHICosttempB = getQueryChannelService().createSqlQuery(bgFckAvgHICostsql).singleResult();
				float bgFckAvgHICosttemp = Float.parseFloat(bgFckAvgHICosttempB.toString());
				float bgFckAvgHICost = (float) (Math.round(bgFckAvgHICosttemp * 100)) / 100;

				/**
				 * 计算妇产科，综合科标杆的指标值 --目前综合科无标杆，无法计算 -------------完成
				 */
				// 获取所在的科室组
				String depCode1 = depTemp.split(",")[0];
				String depGro = "";
				String sqlGetDepN = "select dg.depgroup_desc as depgroup from DRGs.t_department t left join DRGs.t_depgroup dg " + "on t.pardepgroup_code =dg.depgroup_code where t.dep_code='" + depCode1 + "'";
				depGro = getQueryChannelService().createSqlQuery(sqlGetDepN).singleResult().toString();
				// 添加妇产科的其他数据
				for (int e = 0; e < depShowList.size(); e++) {
					Map<String, Object> map = depShowList.get(e);
					if (map.get("DR_OUT_DEP") != null && spDepList1.get(b).toString().equals(map.get("DR_OUT_DEP").toString())) {
						map.put("QUOTA_DETAIL_SUM", lyFckAvgCost);// 次均费用去年同期累计值
						map.put("DEPGROUP_DESC", depGro);// 科室组名称
						map.put("SUM_AVGCOST", fckSumLJMap.get("AVGFEE"));// 累计次均费用
						map.put("BAVGCOST", bgFckAvgCost);// 目标值
						map.put("BAVGDAYS", bgFckAvgDays);// 平均住院日目标值
						map.put("BDRUGCOSTRATIO", bgFckDrugCostRatio);// 药占比目标值
						map.put("BAVGHICOST", bgFckAvgHICost);// 医保花费
						map.put("SUM_OUTSUM", fckSumLJMap.get("TOTALCASECNT"));// 出院人数累计值
						map.put("SUM_AVGBEDDAYS", fckSumLJMap.get("AVGDAYS"));// 平均住院日累计值
						map.put("SUM_DRUGRATION", fckSumLJMap.get("DRUGRATIO"));// 药占比累计值
						depShowList.remove(e);
						depShowList.add(map);
					}
				}
			}
			// -----------------------------------计算添加全院-----------------------------------
			//  拼接科室条件（描述）
			for(String depCode : depCodesList) {
				if(StringUtils.hasLength(depMapCodeName.get(depCode))) {
					depNamesList.add(depMapCodeName.get(depCode)) ;
				}
			}
			String[] depNamesArray = new String[depNamesList.size()];
			depNamesList.toArray(depNamesArray) ;
			String depNames = StringUtils.convertArrayToQuoteString(depNamesArray) ;

			if (depShowList.size() != 0) {
				String sqlAll = "select  sum(outsum) as outsum, sum(total_cost) as t_total_cost, \n " + " round(sum(total_cost)/sum(outsum) ,2)as avgcost , \n " + " round(sum(beddays)/sum(outsum),2 ) as avgbeddays , \n "
						+ " case when sum(total_cost)>0 then round((sum(drug_cost)/sum(total_cost))*100 ,2) || '%' else '0' end as drugratio  ,  \n "
						+ " round(sum(hi_total_cost)/sum(hi_out_sum) ,2) as hiavgcost  from  \n " + " (select aa.*,drgd.dep_code as  adepcode,drgd.pardepgroup_code  as adepgroupcode from  \n "
						+ " (select count(t.dr_total_cost) as outsum, round(sum(t.dr_nodrug_cost), 2) as nodrug_cost,  \n " + " round(sum(t.dr_drug_cost), 2) as drug_cost,round(sum(dr_total_cost), 2) as total_cost,  \n "
						+ " DR_OUT_DEP, round(sum(dr_out_date - dr_in_date), 2) as beddays, count(DR_EPISODE_ID) as patientsum, \n " + " round(sum(dr_out_date - dr_in_date) / count(dr_mr_no),2) as avgbeddays,  \n "
						+ "  round(sum(dr_total_cost) / count(DR_EPISODE_ID), 2) as avgCost, \n " + " case when sum(dr_total_cost)>0 then round(sum(t.dr_drug_cost) / sum(dr_total_cost), 2) else 0 end as drugRation \n "
						+ " from DRGs.T_DAILYRECORD t  where 1=1  \n " + " and to_char(t.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "' and '" + todate + "' and instr('" + hospCode + "',t.dr_hospcode)>0 \n "
						+ "  group by DR_OUT_DEP) aa left join DRGs.t_department drgd   \n " + "  on aa.dr_out_dep=drgd.dep_name) a left join  \n "
						+ " (select round(sum(b.dr_total_cost) /count(b.DR_EPISODE_ID), 2) as hiavgCost, sum(b.dr_total_cost) \n "
						+ " as hi_total_cost ,count(b.DR_EPISODE_ID) as hi_out_sum, DR_OUT_DEP  from DRGs.T_DAILYRECORD b  \n ";
				if (!sp_dep4.isEmpty()) {
					sqlAll += " where b.dr_patient_type in (" + sp_dep4 + ")  \n ";
				}
				sqlAll += " group by b.dr_out_dep) hi  on a.dr_out_dep = hi.dr_out_dep  \n ";
				// 增加科室条件
				if(StringUtils.hasLength(depCodes)) {
					sqlAll = sqlAll.replaceAll("1=1", " 1=1 and t.dr_out_dep in ("+depNames+") ") ;
				}
				Map<String, Object> map = jdbcTemplateObj.queryForMap(sqlAll);
				map.put("DR_OUT_DEP", "全院");
				map.put("DEPGROUP_DESC", "全院");
				map.put("BDRUGCOSTRATIO", "0");
				depShowList.add(0, map);
			}
			// -----------------------------------计算添加全院 结束--------------------------------

		}

		// 科室角色 ，根据授权的科室过滤结果。
		List<Map<String, Object>> authdDpShowList = new ArrayList<>() ;
		if(StringUtils.hasLength(depCodes) && depShowList.size()>0) {
			// 全院的查询结果
			authdDpShowList.add(0, depShowList.get(0));
			// 根据授权科室过滤科室
			for(Map<String, Object>  map : depShowList) {
				//是否是授权科室
				if(depCodesList.contains(map.get("DEP_CODE")) || depCodesList.contains(map.get("DR_OUT_DEP"))) {
					authdDpShowList.add(1,map) ;
				}
			}
			return authdDpShowList ;
		}
		return depShowList;
	}

	/**
	 * @methodname :getDepOnlyShow
	 * @description :
	 * @param :
	 * @author :pc
	 * @return :
	 * @date :2014年9月4日上午11:30:43
	 */
	@Override
	public List<Map<String, Object>> getDepOnlyShow(String fromdate, String todate, String depCode) {
		String lastStartTime = fromdate.substring(5, 10);
		String lastEndTime = todate.substring(5, 10);
		String year = fromdate.substring(0, 4);
		String lastyear = Integer.parseInt(year) - 1 + "";
		String lastDateFrom = lastyear + "-" + lastStartTime;
		String lastDateTo = lastyear + "-" + lastEndTime;

		String sqlAcc = "";
		String sqlAcc2 = "";
		String sqlAcc3 = "";
		String depName = "";

		// 获取科室代码按对应的科室名称
		String getDepName = "select dep_name from DRGs.t_department where dep_code='" + depCode + "'";

		depName = (String) getQueryChannelService().createSqlQuery(getDepName).singleResult();

		// 根据配置判断科室是否在二类科室对应的科室中，如果在的话，查询的代码则为二类科室
		// 取系统配置，取得第二类科室配置
		String dayMonitor_dep = GlobalData.sysConfig.get("DAYMONITOR_DEP");
		// String sp_dep1 = "";
		String sp_dep2 = "";
		String sp_dep3 = "";
		String sp_dep4 = "";
		List<String> spDepList1 = new LinkedList<String>();// 第二类科室名称
		List<String> spDepList2 = new LinkedList<String>();// 第二类科室对应科室代码
		Map<String, Object> mapdep = new HashMap<>();// 第二类科室名称，对应的科室code
		// 对获取的配置做截取处理，处理成二类科室为list，二类科室和对应的科室代码为map的形式
		if (dayMonitor_dep != null && dayMonitor_dep.split("θ").length > 3) {// 配置了第二类科室、第二类科室的对照科室、医保类型
			sp_dep2 = dayMonitor_dep.split("θ")[1];
			sp_dep3 = dayMonitor_dep.split("θ")[2];
			sp_dep4 = dayMonitor_dep.split("θ")[3];
		} else if (dayMonitor_dep != null && dayMonitor_dep.split("θ").length > 2) {// 配置了第二类科室、第二类科室的对照科室
			sp_dep2 = dayMonitor_dep.split("θ")[1];
			sp_dep3 = dayMonitor_dep.split("θ")[2];
		} else if (dayMonitor_dep != null && dayMonitor_dep.split("θ").length > 2) {// 只配置了第二类科室
			sp_dep2 = dayMonitor_dep.split("θ")[1];
		}
		if (!sp_dep2.isEmpty()) {

			spDepList1 = Arrays.asList(sp_dep2.split("&"));
		}
		if (!sp_dep3.isEmpty()) {
			for (int i = 0; i < spDepList1.size(); i++) {
				String sp_dep2temp = sp_dep3.split("&")[i];
				String sp_depA = sp_dep2temp.split(":")[0];
				String sp_depcode = sp_dep2temp.split(":")[1];
				spDepList2 = Arrays.asList(sp_depcode.split(",")); // 将第二类科室对应的科室代码存入list中
				mapdep.put(sp_depA, sp_depcode);// 将第二类科室和其对应的科室代码存入map

				// 确定当前登录科室是否为第二类科室对应的科室代码
				if (spDepList2.contains(depCode)) {
					depCode = "depCode" + i;
					depName = spDepList2.get(i);
				}
			}
		}
		if (!sp_dep4.isEmpty()) {
			sp_dep4 = "'" + sp_dep4 + "'";
			sp_dep4 = sp_dep4.replace("&", "','");
		}

		// 标杆年份
		String manageYear = year;
		String manageYearSql = "select count(t.qm_id) from DRGs.t_Quotamanage t where t.manage_year='" + year + "' and patient_type=0";
		double count = getQueryChannelService().createSqlQuery(manageYearSql).queryResultCount();
		if (count <= 0) {
			manageYearSql = "select max(t.manage_year) manageyear from DRGs.t_Quotamanage t where pardrg_code is null and pardep_code is not null and patient_type=0";
			String mYear = (String) getQueryChannelService().createSqlQuery(manageYearSql).singleResult();
			manageYear = mYear;
		}

		// 下面是调完指标名称的sql，需要对归档表和监测表进行判断，判断归档表是否有数据，有的话就从归档表取。
		sqlAcc = " left join ( \n " + " select a.P00300001 pardep_code, avg(a.P00800000) quota_detail_sum  \n " + " from DRGs.Mr_Ipinfo a  \n " + " where 1=1  \n " + " and to_char(a.P00290000,'yyyy-mm-dd') between '"
				+ lastDateFrom + "' and '" + lastDateTo + "'  \n " + " group by P00300001 ) complete13  \n " + " on a.adepcode = complete13.pardep_code) abc left join DRGs.t_department ddep \n "
				+ " on abc.dr_out_dep = ddep.dep_name left join DRGs.t_depgroup dd  on \n " + " ddep.pardepgroup_code = dd.depgroup_code  \n "
				+ " left join (select dr_out_dep aadep,sum(overbenchmark) overbenchmark ,sum(notover) notover from (  \n "
				+ " select dr.dr_out_dep ,case when dr.dr_total_cost - benchmark.manage_benchmark>0 then  1 else 0 end overbenchmark,case when dr.dr_total_cost-benchmark.manage_benchmark<=0 then 1 else 0 end notover  from drgs.t_dailyrecord dr \n ";

		sqlAcc2 = " left join ( select dep_code,dep_name from  DRGs.t_department ";

		for (int i = 0; i < spDepList1.size(); i++) {
			sqlAcc2 = sqlAcc2 + "union select'dep" + i + "' dep_code,'" + spDepList1.get(i) + "' dep_name from dual  ";
		}
		sqlAcc2 = sqlAcc2 + " )dp on dr.dr_out_dep = dp.dep_name \n ";

		sqlAcc3 = " left join(select qm.pardep_code,qm.manage_benchmark from DRGs.t_quotamanage qm where qm.pardep_code is not null and qm.pardrg_code is null  and qm.patient_type=0 and qm.manage_year = '" + manageYear
				+ "' and qm.parquota_code = 'AvgFee' \n ";

		// + " left join ( select dep_code,dep_name from drgs.t_department union select
		// 'fck' dep_code,'妇产科' dep_name from dual union select 'zhk' dep_code,'综合科'
		// dep_name from dual)dp on dr.dr_out_dep = dp.dep_name \n "
		// + " left join(select qm.pardep_code,qm.manage_benchmark from
		// DRGs.t_quotamanage qm where qm.pardep_code is not null and qm.pardrg_code is
		// null and qm.patient_type=0 and qm.manage_year = '"+manageYear+"' and
		// qm.parquota_code = 'AvgFee' \n "
		// + " union select 'fck' pardep_code,avg(qm.manage_benchmark) manage_benchmark
		// from DRGs.t_quotamanage qm where qm.pardrg_code is null and qm.patient_type=0
		// and qm.manage_year = '"+manageYear+"' and qm.parquota_code = 'AvgFee' and
		// qm.pardep_code in('fk','ck') \n "
		// + " union select 'zhk' pardep_code,avg(qm.manage_benchmark) manage_benchmark
		// from DRGs.t_quotamanage qm where qm.pardrg_code is null and qm.patient_type=0
		// and qm.manage_year = '"+manageYear+"' and qm.parquota_code = 'AvgFee' and
		// qm.pardep_code in('zhk17','zhk18') \n "
		for (int j = 0; j < spDepList1.size(); j++) {
			String depTemp = (String) mapdep.get(spDepList1.get(j));
			depTemp = "'" + depTemp + "'";
			depTemp.replace(",", "','");

			sqlAcc3 = sqlAcc3 + " union select 'dep" + j + "',avg(qm.manage_benchmark) manage_benchmark from DRGs.t_quotamanage qm " + " where qm.pardrg_code is null and qm.patient_type=0 and qm.manage_year = '"
					+ manageYear + "' and qm.parquota_code = 'AvgFee' and qm.pardep_code in(" + depTemp + ") \n";
		}

		sqlAcc3 = sqlAcc3 + " ) benchmark  on dp.dep_code= benchmark.pardep_code  where to_char(dr.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "'  \n "
		// + " ) benchmark on dp.dep_code= benchmark.pardep_code where
		// to_char(dr.dr_out_date,'yyyy-mm-dd') between '"+fromdate+"' \n "
				+ " and '" + todate + "' )group by dr_out_dep) overnum \n " + " on abc.dr_out_dep=overnum.aadep \n " + " order by depgroup_desc \n ";

		String sql = "select abc.OUTSUM,abc.NODRUG_COST,abc.DRUG_COST,abc.TOTAL_COST,abc.DR_OUT_DEP,abc.BEDDAYS,abc.PATIENTSUM, \n "
				+ " abc.AVGBEDDAYS,abc.AVGCOST,abc.DRUGRATION,abc.SUM_OUTSUM,abc.SUM_AVGBEDDAYS,abc.SUM_AVGCOST,abc.SUM_DRUGRATION, \n "
				+ " abc.ADEPCODE,abc.ADEPGROUPCODE,abc.DRUGRATIO,abc.HIAVGCOST,abc.ACCAVGBEDDAYS,abc.ACCAVGCOST,abc.ACCDRUGRATIO, \n "
				+ " abc.PARDEP_CODE,abc.BAVGCOST,abc.BAVGDAYS,abc.BDRUGCOSTRATION,abc.BAVGHICOST,abc.DEP_NAME,abc.BDRUGCOSTRATIO, \n "
				+ " abc.CURSTAND,abc.ACCSTAND,abc.GROW,abc.QUOTA_DETAIL_SUM,abc.DEPIN,abc.DEPOUT, dd.depgroup_desc,overnum.AADEP, \n "
				+ " overnum.OVERBENCHMARK,overnum.NOTOVER  from (select a.*,round(a.drugRation * 100, 4) || '%' as drugRatio, \n "
				+ " hi.hiavgCost, acc.accavgbeddays, acc.accavgcost,round(acc.accdrugration * 100, 2) || '%' as AccdrugRatio, \n "
				+ " quotaM.PARDEP_CODE,quotaM.BAVGCOST,quotaM.BAVGDAYS,quotaM.BDRUGCOSTRATION,quotaM.BAVGHICOST,quotaM.DEP_NAME, \n " + " round(quotaM.bDrugCostRation, 2) || '%' as bDrugCostRatio, \n "
				+ " round(a.avgCost -nvl(quotaM.bAvgCost,0), 2) as curStand, round(a.avgCost - acc.AccavgCost, 2) as accStand, \n "
				// -- 解决除数为0问题
				+ " round(( decode(complete13.quota_detail_sum,0,null,(a.avgCost - complete13.quota_detail_sum) / complete13.quota_detail_sum)), 2) as grow, \n "
				+ " round(complete13.quota_detail_sum, 2) as quota_detail_sum, nvl(depin.incnt,0) as depin,nvl(depout.outcnt,0) as depout from  \n "
				// --本期值计算
				+ " (select aa.OUTSUM,aa.NODRUG_COST,aa.DRUG_COST,aa.TOTAL_COST,aa.DR_OUT_DEP,aa.BEDDAYS,aa.PATIENTSUM,aa.AVGBEDDAYS,aa.AVGCOST,aa.DRUGRATION, \n "
				+ " aa1.sum_outsum,aa1.sum_avgbeddays,aa1.sum_avgCost,aa1.sum_drugRation,drgd.dep_code as  adepcode,drgd.pardepgroup_code  as adepgroupcode from \n "
				+ " (select count(t.dr_total_cost) as outsum, round(sum(t.dr_nodrug_cost), 2) as nodrug_cost, \n " + " round(sum(t.dr_drug_cost), 2) as drug_cost,round(sum(dr_total_cost), 2) as total_cost, \n "
				+ " DR_OUT_DEP, round(sum(dr_out_date - dr_in_date), 2) as beddays, count(DR_EPISODE_ID) as patientsum, \n " + " round(sum(dr_out_date - dr_in_date) / count(dr_mr_no),2) as avgbeddays, \n "
				+ " round(sum(dr_total_cost) / count(DR_EPISODE_ID), 2) as avgCost,round(sum(t.dr_drug_cost) / sum(dr_total_cost), 4) as drugRation \n " + " from DRGs.T_DAILYRECORD t  where 1=1  \n "
				+ " and to_char(t.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "' and '" + todate + "'  \n " + " and dr_out_dep='" + depName + "'  group by DR_OUT_DEP) aa  \n "
				// 计算累计数据
				/*
				 * +" left join (select count(t.dr_total_cost) as sum_outsum,DR_OUT_DEP,round(sum(dr_out_date - dr_in_date) /count(dr_mr_no), 2) as sum_avgbeddays,  \n "
				 * +" round(sum(dr_total_cost) / count(DR_EPISODE_ID),2) as sum_avgCost,to_char(round(sum(t.dr_drug_cost) / sum(dr_total_cost)*100,2)||'%') as sum_drugRation  \n "
				 * +" from DRGs.T_DAILYRECORD t  where 1=1  \n ";
				 */
				+ " left join (select count(t.P00800000) as sum_outsum,P00300000 OUT_DEP,round(sum(T.P00290000 - T.P00250000) /count(T.pmainindex), 2) as sum_avgbeddays,  \n "
				+ " round(sum(t.P00800000) / count(T.pmainindex),2) as sum_avgCost,to_char(round(sum(es.drug_cost) / sum(T.P00800000)*100,2)||'%') as sum_drugRation  \n "
				+ " from DRGs.mr_ipinfo t left join drgs.mr_expense_sum es on t.pmainindex = es.pmainindex  where 1=1  \n " + " and to_char(t.P00290000,'yyyy-mm-dd') between '" + year + "-01-01' and '" + todate + "' \n "
				+ " group by P00300000) aa1 on aa1.OUT_DEP=aa.dr_out_dep  \n " + " left join DRGs.t_department drgd on aa.dr_out_dep=drgd.dep_name) a  \n "
				// --医保数据计算
				+ " left join (select round(sum(b.dr_total_cost) /count(b.DR_EPISODE_ID), 2) as hiavgCost, \n " + " DR_OUT_DEP  from DRGs.T_DAILYRECORD b   \n ";
		if (!sp_dep4.isEmpty()) {
			sql += " where b.dr_patient_type in (" + sp_dep4 + ") \n";
		}
		sql += " group by b.dr_out_dep) hi  on a.dr_out_dep = hi.dr_out_dep \n "
				// -- 累计值计算--下列代码计算的是当前查询日期的累计值
				+ " left join ( \n " + " select dep_name, dep_code, avgdays accavgbeddays, avgfee accavgcost,  drugRatio accdrugration, hcAvgFee accAvgHICost \n " + " from(  \n "
				+ " select a.DEP_CODE,a.DEP_NAME,a.TOTALFEE,a.DRUGFEE,a.AVGDAYS,a.AVGFEE,a.HCCOSTAMOUNT,a.HCCOUNT, \n " + " case when TotalFee<>0 then DrugFee/TotalFee*100 else 0 end drugRatio, \n "
				+ " case when hccount=0 then 0 else hccostamount/hccount end hcAvgFee \n " + " from ( select a.P00300001 dep_code, a.P00300000 dep_name, \n " + " sum(a.P00800000)/10000 as TotalFee,  \n "
				+ " sum(es.drug_cost)/10000 as DrugFee, \n " + " avg(P00320000) avgdays,  \n " + " avg(P00800000) avgfee, \n " + " sum(case when d.parid is not null then P00800000 else 0 end) hccostamount,  \n "
				+ " sum(case when d.parid is not null then 1 else 0 end) hccount  \n " + " from DRGs.Mr_Ipinfo a  \n " + " left join DRGs.t_patientdrg b  \n " + " on a.pmainindex=b.parid  \n "
				+ " left join DRGs.t_medins d  \n " + " on d.parid=a.pmainindex  left join drgs.mr_expense_sum es on a.pmainindex = es.pmainindex \n " + " where 1=1  \n " + " and to_char(a.P00290000,'yyyy')='" + year
				+ "'  \n " + " group by a.P00300001, a.P00300000 \n " + " ) a))acc on a.dr_out_dep = acc.dep_name \n "
				// --标杆值
				+ " left join (select quotaM.PARDEP_CODE,quotaM.BAVGCOST,quotaM.BAVGDAYS,quotaM.BDRUGCOSTRATION,quotaM.BAVGHICOST, \n " + " dep.dep_name   from (select pardep_code, round(max(decode(parquota_code, \n "
				+ " 'AvgFee', manage_benchmark, 0)),2) bAvgCost, round(max(decode(parquota_code,'AvgDays', manage_benchmark, \n " + " 0)), 2) bAvgDays, round(max(decode(parquota_code, 'DrugRatio', manage_benchmark, \n "
				+ " 0)), 2) bDrugCostRation,round(max(decode(parquota_code, 'HCAvgFee', manage_benchmark, 0)),2) bAvgHICost \n "
				+ " from DRGs.t_quotamanage where pardep_code is not null and pardrg_code is null and patient_type=0 \n " + " and manage_year = '" + manageYear
				+ "' group by pardep_code) quotaM,  DRGs.t_department dep \n " + " where quotaM.pardep_code = dep.dep_code) quotaM  on a.dr_out_dep = quotaM.dep_name \n "
				// --科室转入
				+ "  left join (select distinct(t.dep_code), count(*) over(partition by t.dep_code) incnt \n " + " from drgs.T_DEPARTMENT t left join drgs.t_dailyrecord d \n "
				+ "  on t.dep_name = d.dr_out_dep where t.dep_name!=d.dr_in_dep  \n " + " and to_char(d.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "' and '" + todate + "'  \n " + " ) depin \n "
				+ " on a.adepcode=depin.dep_code \n "
				// 科室转出
				+ " left join (select distinct(t.dep_code),   count(*) over(partition by t.dep_code) outcnt \n " + "  from drgs.T_DEPARTMENT t left join drgs.t_dailyrecord d  on t.dep_name = d.dr_in_dep \n "
				+ "  where t.dep_name!=d.dr_out_dep  \n " + " and to_char(d.dr_out_date,'yyyy-mm-dd') between '" + fromdate + "' and '" + todate + "'  \n " + "  )depout on a.adepcode=depout.dep_code \n ";

		// 将两个sql连接进行查询
		sql = sql + sqlAcc + sqlAcc2 + sqlAcc3;

		List<Map<String, Object>> depShowList = jdbcTemplateObj.queryForList(sql);

		if (depShowList.size() != 0) {

			for (int b = 0; b < spDepList1.size(); b++) {
				String depTemp = (String) mapdep.get(spDepList1.get(b));
				String depTempC = "";
				depTempC = "'" + depTemp + "'";
				depTempC = depTempC.replace(",", "','");

				/**
				 * 计算妇产科和综合科的次均费用--【去年】
				 */
				// 查询妇科和产科的患者总例数的和
				String sqlfckSum = "";
				sqlfckSum = " select count(t.pmainindex) from DRGs.mr_ipinfo t where 1=1 ";
				sqlfckSum += " and to_char(t.P00290000,'yyyy-mm-dd') between '" + fromdate + "' and '" + todate + "' ";
				sqlfckSum += " and (t.P00300001 in (" + depTempC + ") )";

				Object fckSumB = getQueryChannelService().createSqlQuery(sqlfckSum).singleResult();

				// 查询去年妇科和产科次均费用
				String sqlfckFee = "";
				sqlfckFee = " select avg(P00800000) AvgFee from DRGs.mr_ipinfo t where 1=1 ";
				sqlfckFee += " and to_char(P00290000, 'yyyy-mm') = '" + lastyear + "-12' ";
				sqlfckFee += " and (t.P00300001 in (" + depTempC + ") )";

				Object lastyearFckFeeB = getQueryChannelService().createSqlQuery(sqlfckFee).singleResult();
				float lyFckAvgCost = 0f;
				if (lastyearFckFeeB != null) {
					float lastyearFckFee = Float.parseFloat(lastyearFckFeeB.toString());
					lyFckAvgCost = (float) (Math.round(lastyearFckFee * 100)) / 100;
				}

				/**
				 * 计算妇产科和综合科的次均费用--【去年】--完成
				 */
				/**
				 * 计算妇产科，综合科当期累计的指标值
				 */
				// 查询妇科和产科的累计次均费用、平均住院日、出院病人、药占比
				String sqlfckSumLJ = "";
				sqlfckSumLJ = " select round(avg(P00320000),2) avgdays,round(avg(P00800000),2) avgfee,count(1) as totalCaseCnt, "
						+ " round(case when sum(a.P00800000)<>0 then sum(es.DRUG_COST)/sum(a.P00800000)*100 else 0 end ,2) drugRatio"
						+ " from DRGs.mr_ipinfo a  left join drgs.mr_expense_sum es on a.pmainindex = es.pmainindex " + " where 1=1 " + " and to_char(P00290000, 'yyyy') = '" + year + "' " + " and (P00300001 in ("
						+ depTempC + "))";
				Map<String, Object> fckSumLJMap = jdbcTemplateObj.queryForMap(sqlfckSumLJ);

				/**
				 * 计算妇产科，综合科累计的指标值 ----------- -------------完成
				 */
				/**
				 * 计算妇产科，综合科标杆的指标值
				 */
				// 次均费用
				String bgFcksql = "select nvl(sum(manage_benchmark)/2,0) as bgavgcost from DRGs.t_quotamanage t " + " where t.pardrg_code is null and t.pardepgroup_code is null and patient_type=0 and pardep_code in ("
						+ depTempC + ") " + " and parquota_code='AvgFee'" + " and manage_year= '" + manageYear + "'";
				Object bgFckAvgCosttempB = getQueryChannelService().createSqlQuery(bgFcksql).singleResult();
				float bgFckAvgCosttemp = Float.parseFloat(bgFckAvgCosttempB.toString());
				float bgFckAvgCost = (float) (Math.round(bgFckAvgCosttemp * 100)) / 100;

				// 平均住院日
				String bgFckAvgDayssql = "select nvl(sum(manage_benchmark)/2,0) as bgavgdays from DRGs.t_quotamanage t "
						+ "where t.pardrg_code is null and t.pardepgroup_code is null and patient_type=0 and pardep_code in (" + depTempC + ") " + "and parquota_code='AvgDays'" + " and manage_year=  '" + manageYear
						+ "'";
				Object bgFckAvgDaystempB = getQueryChannelService().createSqlQuery(bgFckAvgDayssql).singleResult();
				float bgFckAvgDaystemp = Float.parseFloat(bgFckAvgDaystempB.toString());
				float bgFckAvgDays = (float) (Math.round(bgFckAvgDaystemp * 100)) / 100;

				// 药占比
				String bgFckDrugCostRatiosql = "select round(a.bgavgcost, 2) || '%'  as bgavgcost from (" + " select sum(manage_benchmark)/2 as bgavgcost from DRGs.t_quotamanage t "
						+ " where t.pardrg_code is null and t.pardepgroup_code is null and patient_type=0 and pardep_code in (" + depTempC + ") " + " and manage_year= '" + manageYear + "' "
						+ " and parquota_code='DrugRatio') a";
				String bgFckDrugCostRatio = getQueryChannelService().createSqlQuery(bgFckDrugCostRatiosql).singleResult().toString();

				String bgFckAvgHICostsql = "select nvl(sum(manage_benchmark)/2,0) as bgavgcost from DRGs.t_quotamanage t "
						+ "where t.pardrg_code is null and t.pardepgroup_code is null and patient_type=0 and pardep_code in (" + depTempC + ") " + " and manage_year= '" + manageYear + "' "
						+ "and parquota_code='HCAvgFee'";
				Object bgFckAvgHICosttempB = getQueryChannelService().createSqlQuery(bgFckAvgHICostsql).singleResult();
				float bgFckAvgHICosttemp = Float.parseFloat(bgFckAvgHICosttempB.toString());
				float bgFckAvgHICost = (float) (Math.round(bgFckAvgHICosttemp * 100)) / 100;

				/**
				 * 计算妇产科，综合科标杆的指标值 --目前综合科无标杆，无法计算 -------------完成
				 */
				// 获取所在的科室组
				String depCode1 = depTemp.split(",")[0];
				String depGro = "";
				String sqlGetDepN = "select dg.depgroup_desc as depgroup from DRGs.t_department t left join DRGs.t_depgroup dg " + "on t.pardepgroup_code =dg.depgroup_code where t.dep_code='" + depCode1 + "'";

				depGro = getQueryChannelService().createSqlQuery(sqlGetDepN).singleResult().toString();

				// 获取添加位置
				int inserN = 0;
				String getD = "select dep_code from DRGs.t_department t " + "left join DRGs.t_depgroup d on t.pardepgroup_code=d.depgroup_code " + "where d.depgroup_desc='" + depGro + "' and  rownum<2";
				Object depDTemp = getQueryChannelService().createSqlQuery(getD).singleResult();
				String indertDep = depDTemp.toString();

				for (int d = 0; d < depShowList.size(); d++) {
					Map<String, Object> map = depShowList.get(d);
					if (map.get("DR_OUT_DEP") != null && indertDep.equals(map.get("DR_OUT_DEP").toString())) {
						inserN = d;
					}
				}

				// 添加妇产科的其他数据
				for (int e = 0; e < depShowList.size(); e++) {
					Map<String, Object> map = depShowList.get(e);
					if (map.get("DR_OUT_DEP") != null && spDepList1.get(b).equals(map.get("DR_OUT_DEP").toString())) {
						float avgCost = 0;
						avgCost = Float.parseFloat(map.get("AVGCOST").toString());
						map.put("QUOTA_DETAIL_SUM", lyFckAvgCost);// 次均费用去年同期累计值
						map.put("DEPGROUP_DESC", depGro);// 科室组名称
						map.put("SUM_AVGCOST", fckSumLJMap.get("AVGFEE"));// 累计次均费用
						map.put("BAVGCOST", bgFckAvgCost);// 目标值
						map.put("BAVGDAYS", bgFckAvgDays);// 平均住院日目标值
						map.put("BDRUGCOSTRATIO", bgFckDrugCostRatio);// 药占比目标值
						map.put("BAVGHICOST", bgFckAvgHICost);// 医保花费
						map.put("SUM_OUTSUM", fckSumLJMap.get("TOTALCASECNT"));// 出院人数累计值
						map.put("SUM_AVGBEDDAYS", fckSumLJMap.get("AVGDAYS"));// 平均住院日累计值
						map.put("SUM_DRUGRATION", fckSumLJMap.get("DRUGRATIO"));// 药占比累计值
						depShowList.remove(e);
						depShowList.add(inserN, map);
					}
				}
			}

		}

		return depShowList;
	}

	/**
	 *
	 * @methodname :getChangeDetail
	 * @description :病例明细
	 * @param :
	 * @author :sulina
	 * @return :
	 * @date :2015年7月10日
	 */
	@Override
	public List<Map<String, Object>> getChangeDetail(String fromdate, String todate, String depName, String type) {
		String sql = " ";
		String dateFrom = fromdate;
		String dateTo = todate;
		String depDesc = depName;
		String deptype = type;

		String manageYear = drgsIndexCalApplication.getRecentlyManageYear(todate.substring(0,4),"manager_inner","dep");

		String dayMonitor_dep = GlobalData.sysConfig.get("DAYMONITOR_DEP");
		String sp_dep2 = "";
		String sp_dep3 = "";
		List<String> spDepList1 = new LinkedList<String>();// 第二类科室名称
		Map<String, Object> mapdep = new HashMap<>();// 第二类科室名称，对应的科室code
		// 对获取的配置做截取处理，处理成二类科室为list，二类科室和对应的科室代码为map的形式
		if (dayMonitor_dep != null && dayMonitor_dep.split("θ").length > 1) {
			sp_dep2 = dayMonitor_dep.split("θ")[1];
			sp_dep3 = dayMonitor_dep.split("θ")[2];
		}
		if (!sp_dep2.isEmpty()) {

			spDepList1 = Arrays.asList(sp_dep2.split("&"));
		}
		if (!sp_dep3.isEmpty()) {
			for (int i = 0; i < spDepList1.size(); i++) {
				String sp_dep2temp = sp_dep3.split("&")[i];
				String sp_depA = sp_dep2temp.split(":")[0];
				String sp_depcode = sp_dep2temp.split(":")[1];
				mapdep.put(sp_depA, sp_depcode);
			}
		}

		if ("in".equals(deptype)) {
			// 入院和出院不等，且出院等于某科室，则为转入
			sql = " select DR_EPISODE_ID,DR_MR_NO,DR_PATIENT_NAME,to_char(DR_IN_DATE,'yyyy-mm-dd') DR_IN_DATE,to_char(DR_OUT_DATE,'yyyy-mm-dd') DR_OUT_DATE,"
					+ " DR_PATIENT_TYPE,DR_IN_DEP,DR_OUT_DEP,DR_DRUG_COST,DR_NODRUG_COST,DR_TOTAL_COST,DR_IS_ABNORMAL,DR_IN_HISDEP,DR_OUT_HISDEP,DR_GROUP_NAME,DR_PROTOMEDICUS from drgs.t_dailyrecord where 1=1 ";
			sql += " and to_char(dr_out_date,'yyyy-mm-dd') between '" + dateFrom + "' and '" + dateTo + "' ";
			sql += " and dr_out_dep='" + depDesc + "' and dr_in_dep!=dr_out_dep order by dr_in_dep";
		} else if ("out".equals(deptype)) {
			// 入院和出院不等，且入院等于某科室，则为转出
			sql = "select DR_EPISODE_ID,DR_MR_NO,DR_PATIENT_NAME,to_char(DR_IN_DATE,'yyyy-mm-dd') DR_IN_DATE,to_char(DR_OUT_DATE,'yyyy-mm-dd') DR_OUT_DATE,"
					+ " DR_PATIENT_TYPE,DR_IN_DEP,DR_OUT_DEP,DR_DRUG_COST,DR_NODRUG_COST,DR_TOTAL_COST,DR_IS_ABNORMAL,DR_IN_HISDEP,DR_OUT_HISDEP,DR_GROUP_NAME,DR_PROTOMEDICUS from drgs.t_dailyrecord where 1=1 ";
			sql += " and to_char(dr_out_date,'yyyy-mm-dd') between '" + dateFrom + "' and '" + dateTo + "' ";
			sql += " and dr_in_dep='" + depDesc + "'  " + "and dr_in_dep!=dr_out_dep order by dr_in_dep";
		} else if ("over".equals(type)) {// 超标
			sql =   "select * from(\n" +
					"select\n" + 
					"dr_out_dep dep_code, dr_out_dep dep_name,\n" + 
					"DR_EPISODE_ID,DR_MR_NO,DR_PATIENT_NAME,to_char(DR_IN_DATE,'yyyy-mm-dd') DR_IN_DATE,to_char(DR_OUT_DATE,'yyyy-mm-dd') DR_OUT_DATE,DR_PATIENT_TYPE,DR_IN_DEP,DR_OUT_DEP,DR_DRUG_COST,DR_NODRUG_COST,DR_IS_ABNORMAL,DR_IN_HISDEP,DR_OUT_HISDEP,DR_GROUP_NAME,DR_PROTOMEDICUS,\n" + 
					"dr_total_cost total_cost,\n" + 
					"avg(c.manage_benchmark) manage_avgfee,\n" + 
					"dr_out_date out_date,\n" + 
					"dr_hospcode p00010001\n" + 
					"from\n" + 
					"drgs.t_dailyrecord a\n" + 
					"left join drgs.t_department b on (a.dr_out_dep=b.dep_name or a.dr_out_dep=b.dep_his2)\n" + 
					"left join (\n" + 
					"select pardep_code, manage_benchmark, row_number() over(partition by pardep_code, parquota_code order by manage_benchmark desc) rn from drgs.t_quotamanage c\n" + 
					"where c.patient_type='0' and c.par_ms_code='manager_inner'\n" + 
					"and c.manage_year='2018' and c.parquota_code='AvgFee'\n" + 
					"and pardrg_code is null and pardep_code is not null\n" + 
					") c on b.dep_code=c.pardep_code and rn=1\n" + 
					"where to_char(a.dr_out_date, 'yyyy-mm-dd') between '" + dateFrom + "' and '" + dateTo + "'\n" +
					"and dr_out_dep='" + depDesc + "'" +
					"group by dr_out_dep, dr_out_dep,\n" + 
					"dr_out_date, dr_hospcode,\n" + 
					"DR_EPISODE_ID,DR_MR_NO,DR_PATIENT_NAME,to_char(DR_IN_DATE,'yyyy-mm-dd'),to_char(DR_OUT_DATE,'yyyy-mm-dd'),DR_PATIENT_TYPE,DR_IN_DEP,DR_OUT_DEP,DR_DRUG_COST,DR_NODRUG_COST,DR_IS_ABNORMAL,DR_IN_HISDEP,DR_OUT_HISDEP,DR_GROUP_NAME,DR_PROTOMEDICUS,\n" + 
					"dr_total_cost\n" + 
					") where total_cost>manage_avgfee";
		} else if ("notover".equals(type)) {// 未超标
			sql =   "select * from(\n" +
					"select\n" + 
					"dr_out_dep dep_code, dr_out_dep dep_name,\n" + 
					"DR_EPISODE_ID,DR_MR_NO,DR_PATIENT_NAME,to_char(DR_IN_DATE,'yyyy-mm-dd') DR_IN_DATE,to_char(DR_OUT_DATE,'yyyy-mm-dd') DR_OUT_DATE,DR_PATIENT_TYPE,DR_IN_DEP,DR_OUT_DEP,DR_DRUG_COST,DR_NODRUG_COST,DR_IS_ABNORMAL,DR_IN_HISDEP,DR_OUT_HISDEP,DR_GROUP_NAME,DR_PROTOMEDICUS,\n" + 
					"dr_total_cost total_cost,\n" + 
					"avg(c.manage_benchmark) manage_avgfee,\n" + 
					"dr_out_date out_date,\n" + 
					"dr_hospcode p00010001\n" + 
					"from\n" + 
					"drgs.t_dailyrecord a\n" + 
					"left join drgs.t_department b on (a.dr_out_dep=b.dep_name or a.dr_out_dep=b.dep_his2)\n" + 
					"left join (\n" + 
					"select pardep_code, manage_benchmark, row_number() over(partition by pardep_code, parquota_code order by manage_benchmark desc) rn from drgs.t_quotamanage c\n" + 
					"where c.patient_type='0' and c.par_ms_code='manager_inner'\n" + 
					"and c.manage_year='2018' and c.parquota_code='AvgFee'\n" + 
					"and pardrg_code is null and pardep_code is not null\n" + 
					") c on b.dep_code=c.pardep_code and rn=1\n" + 
					"where to_char(a.dr_out_date, 'yyyy-mm-dd') between '" + dateFrom + "' and '" + dateTo + "'\n" +
					"and dr_out_dep='" + depDesc + "'" +
					"group by dr_out_dep, dr_out_dep,\n" + 
					"dr_out_date, dr_hospcode,\n" + 
					"DR_EPISODE_ID,DR_MR_NO,DR_PATIENT_NAME,to_char(DR_IN_DATE,'yyyy-mm-dd'),to_char(DR_OUT_DATE,'yyyy-mm-dd'),DR_PATIENT_TYPE,DR_IN_DEP,DR_OUT_DEP,DR_DRUG_COST,DR_NODRUG_COST,DR_IS_ABNORMAL,DR_IN_HISDEP,DR_OUT_HISDEP,DR_GROUP_NAME,DR_PROTOMEDICUS,\n" + 
					"dr_total_cost\n" + 
					") where total_cost<=manage_avgfee";
		}
		List<Map<String, Object>> depcaseList = jdbcTemplateObj.queryForList(sql);

		return depcaseList;
	}

	/**
	 *
	 * @methodname :getLastestIssue
	 * @description :
	 * @param :
	 * @author :fulianxia
	 * @return :
	 * @date :2015年5月11日
	 */
	@Override
	public String getLastestIssue(String hospCode) {
		String jpql = "SELECT to_char(max(t.dr_out_date),'yyyy-mm-dd') FROM DRGS.t_Dailyrecord t ";
		if (StringUtils.hasLength(hospCode)) {
			jpql += " where instr('" + hospCode + "',t.dr_hospcode)>0  ";
		}
		return jdbcTemplateObj.queryForObject(jpql, String.class);
	}

	/**
	 *
	 * @methodname :getIsAbnormal
	 * @description :
	 * @param :
	 * @author :fulianxia
	 * @return :
	 * @date :2015年5月12日
	 */
	@Override
	public int getIsAbnormal(String fromDate, String toDate, String hospCode, String depCodes) {
		String sql = "select count(*) as pesum  from DRGs.T_DAILYRECORD  a\r\n" + "left join drgs.t_department dep on a.dr_out_dep=dep.dep_name\r\n" + "where  a.dr_is_abnormal='1' \n";// and
																																														// instr('"+hospCode+"',dr_hospcode)>0
																																														// ";
		sql += " and ( to_char(dr_out_date,'yyyy-mm-dd') between '" + fromDate + "' and '" + toDate + "')";
		if (StringUtils.hasLength(depCodes)) {
			depCodes = "'" + depCodes.replace(",", "','") + "'";
			sql += " and dep.dep_code in(" + depCodes + ") ";
		}
		return jdbcTemplateObj.queryForObject(sql, Integer.class);
	}

	/**
	 *
	 * @Description：查询日监测数据
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.DailyRecordApplication#findDailyRecords()
	 */
	public List<Object[]> findDailyRecords(String hospCode) {
		String sql = "select t.dr_episode_id,t.dr_mr_no,t.dr_patient_name,t.dr_patient_type,t.dr_in_hisdep,t.dr_out_hisdep,";
		sql += "to_char(t.dr_out_date,'yyyy-mm-dd'),t.dr_is_abnormal from DRGs.T_DAILYRECORD t where t.dr_is_abnormal='1'";// and instr('"+hospCode+"',dr_hospcode)>0 ";
		List<Object[]> dailyRecords = getQueryChannelService().createSqlQuery(sql).list();
		return dailyRecords;
	}

	/*
	 * (non-Javadoc)
	 *
	 * @see
	 * com.dhcc.DRGsHosp.application.DailyRecordApplication#getNoDataDay(java.lang.
	 * String, java.lang.String)
	 */
	@Override
	public List<String> haveDataDayList(String yesterday, String lastMonth, String hospCode) {
		String sql = "";
		sql += "select distinct  to_char(dr_out_date,'yyyy-mm-dd') as out_date from DRGs.T_DAILYRECORD t \n" + " where to_char(dr_out_date,'yyyy-mm-dd') between '" + lastMonth + "' and '" + yesterday + "' and instr('"
				+ hospCode + "',dr_hospcode)>0 ";
		List<String> resultList = getQueryChannelService().createSqlQuery(sql).list();
		return resultList;
	}

	/**
	 *
	 * @Description：修改异常数据
	 * @Author lhj
	 * @see com.dhcc.DRGsHosp.application.DailyRecordApplication#saveOrUpdate(String,
	 *      String, String)
	 */
	@Override
	public void saveOrUpdate(String drOutDep, String drIsAbnormal, String drEpisodeId, String hospCode) {
		String jpql = "select DR from DailyRecord DR where DR.dr_episode_id  = '" + drEpisodeId + "'";
		DailyRecord dailyRecord = (DailyRecord) getQueryChannelService().createJpqlQuery(jpql).singleResult();
		dailyRecord.setDr_out_dep(drOutDep);
		dailyRecord.setDr_is_abnormal(Double.valueOf(drIsAbnormal));
		dailyRecord.setDr_hospcode(hospCode);
		dailyRecord.save();
	}

}
